﻿<cfsilent>
	<cfscript>
		
		markAdvice = getProperty("serviceFactory").getBean("markAdvice");
		dictAdvice = getProperty("serviceFactory").getBean("senateDictionaryAdvice");
		
		sessionAdvice = getProperty("serviceFactory").getBean("sessionAdvice");
		depId = sessionAdvice.getUserProp("teacherDepartment");
		
		studentId = event.getArg("STU");
		
		sql = "SELECT a.stu_name
				FROM t_student a 
				WHERE a.stu_id = :studentId ";
				
		queryObj = new Query( datasource=application.dnsSlave ) ;
   		queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
		
		rs_student= queryObj.execute( sql=sql ).getResult();
	
		sql = "SELECT 
						b.academic_year, b.academic_term, 
						a.task_type,c.course_name, 
						c.course_code, c.course_credit, 
						d.prop_name, a.mark_mode, 
						a.mark_result, a.mark_note, 
						a.mark_resit, a.mark_renote, 
						a.mark_resit_limit,a.retaking 
				FROM t_student_mark a 
					 INNER JOIN t_term b ON a.term_id = b.term_id 
					 INNER JOIN t_course c ON a.cid = c.cid 
					 INNER JOIN t_course_prop d ON a.prop_id = d.prop_id  
					 INNER JOIN t_student ON t_student.stu_id = a.stu_id 
					 INNER JOIN t_class ON t_class.cls_id = t_student.cls_id 
					 INNER JOIN t_subject ON t_class.sbj_id = t_subject.sbj_id  
				WHERE 
					a.stu_id = :studentId 
					AND 
					t_subject.institute_id = :depId 
				ORDER BY b.academic_year DESC, b.term_id, a.prop_id, c.course_name ";
				
		queryObj = new Query( datasource=application.dnsSlave ) ;
   		queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
   		queryObj.addParam( name="depId", value=depId, cfsqltype="cf_sql_varchar" );
		
		rs_mark= queryObj.execute( sql=sql ).getResult();
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat( "学生成绩-" & studentId, "utf-8" );
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew( "成绩信息", false );
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学年,学期,学号,姓名,开课类别,课程,课程号,学分,课程性质,成绩,补考成绩,重修");
		
		row = 1;
		
	//	for ( mark in markArray ) {
		for ( a=1; a LTE rs_mark.recordCount; a++ ){
			
			row++;
			
			/* markMode, mark, markNote */
			finalResult = markAdvice.getMark( rs_mark["mark_mode"][a], rs_mark["mark_result"][a], rs_mark["mark_note"][a] );

			/* 正考成绩 */
			if ( finalResult eq "" ) {
				finalResult = " ";
			}
			
			/* markMode, mark, markNote, markLimit */
			resitResult = markAdvice.getMarkResit( rs_mark["mark_mode"][a], rs_mark["mark_resit"][a], rs_mark["mark_renote"][a], rs_mark["mark_resit_limit"][a] );
			
			/* 补考成绩 */
			if ( resitResult eq "" ) {
				resitResult = " ";
			}
			
			resit = "";
			if ( rs_mark["retaking"][a] eq "1" ) {
				resit = "是";
			}
			
			/* 构建成绩数据 */
			rowData = rs_mark["academic_year"][a]
						& "," & dictAdvice.getTerm( rs_mark["academic_term"][a] )
						& "," & studentId
						& "," & rs_student.stu_name
						& "," & dictAdvice.getTaskType( rs_mark["task_type"][a] )
						& "," & rs_mark["course_name"][a]
						& "," & rs_mark["course_code"][a]
						& "," & numberFormat( rs_mark["course_credit"][a], "_.__" )
						& "," & rs_mark["prop_name"][a]
						& "," & finalResult
						& "," & resitResult
						& "," & resit;
			
			/* 写入学生信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);

	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>